package com.icw.utility.excel;

import java.io.ByteArrayOutputStream;
import java.io.Closeable;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.icw.utility.excel.base.ExcelPortA;
import com.icw.utility.excel.base.ExcelPortI;

public class ExcelPort extends ExcelPortA {

	private Workbook book;

	private Sheet sheet;

	private int rowNum, colNum, rowNumMax, colNumMax;

	private boolean needsValidate = false;

	public ExcelPort(Collection<ExcelHeader> hs, Collection<Map<String, Object>> ds) {
		super(hs, ds);
	}

	public void validate(InputStream i) throws Exception {
		try {
			rowNum = colNum = rowNumMax = colNumMax = 0;
			sheet = createBook(i).getSheetAt(0);
			needsValidate = true;
			fillHeaders(this.getHeaders());// 移动到数据区
		} finally {
			closeCloseable(i);
			needsValidate = false;
		}
	}

	private Workbook createBook() {
		return book = new XSSFWorkbook();
	}

	private Workbook createBook(InputStream i) throws Exception {
		return book = WorkbookFactory.create(i);
	}

	// 输入流变成List-Map
	public void in(InputStream i) throws Exception {
		try {
			rowNum = colNum = rowNumMax = colNumMax = 0;
			sheet = createBook(i).getSheetAt(0);
			removeMerge();
			fillHeaders(this.getHeaders());// 移动到数据区
			this.rowNum = rowNumMax + 1;
			this.colNum = 0;
			getDatas().clear();
			readDatas(getDatas(), getHeaders());
		} finally {
			closeCloseable(i);
		}
	}

	public byte[] out() throws Exception {
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		out(out);
		return out.toByteArray();
	}

	// List-Map变成输出流
	public void out(OutputStream o) throws Exception {
		try {
			rowNum = colNum = rowNumMax = colNumMax = 0;
			sheet = createBook().createSheet();
			fillHeaders(this.getHeaders());
			this.rowNum = rowNumMax + 1;
			this.colNum = 0;
			fillDatas(getDatas(), getHeaders());
			merge_row();
			merge_col();
			autoWidth();
			book.write(o);
			o.close();
		} finally {
			closeCloseable(o);
		}
	}

	private void readDatas(final Collection<Map<String, Object>> c, final Collection<ExcelHeader> headers) throws Exception {
		// 递归改为循环，防止内存溢出
		while (readDatasUnit(c, headers)) {
		}
	}

	private boolean readDatasUnit(final Collection<Map<String, Object>> c, final Collection<ExcelHeader> headers) throws Exception {
		lock_col(() -> {
			final Map<String, Object> map = new HashMap<String, Object>();
			for (Iterator<ExcelHeader> it = headers.iterator(); it.hasNext(); moveRight()) {
				final ExcelHeader h = it.next();
				if (h.getFieldName() != null && !"".equals(h.getFieldName())) {
					lock_row(new LockAction() {

						@Override
						public void execute() {
							map.put(h.getFieldName(), h.getHeaders() == null ? getCellValue() : new LinkedList<Map<String, Object>>());
						}
					});
				}
			}
			c.add(map);
		});
		moveDown();
		if (rowNum > sheet.getLastRowNum() || !isEmptyLeft(colNum)) {// 超过范围||同行左侧列不全为空 ，程序终止
			rowNum--;// 回退一行
			return false;
		} else {
			return true;
		}
	}

	private boolean isEmptyLeft(int colEndNum) {
		for (int i = 0; i < colEndNum; i++) {
			Cell cell_i = sheet.getRow(rowNum).getCell(i);
			if (cell_i == null || cell_i.getStringCellValue() == null || "".equals(cell_i.getStringCellValue())) {
				continue;
			} else {
				return false;
			}
		}
		return true;
	}

	private void fillDatas(final Collection<Map<String, Object>> list, final Collection<ExcelHeader> headers) throws Exception {
		for (Iterator<Map<String, Object>> it_data = list.iterator(); it_data.hasNext(); moveDown()) {
			final Map<String, Object> _temp = it_data.next();
			lock_col(() -> fillData(_temp, headers));
		}
	}

	@SuppressWarnings("unchecked")
	private void fillData(final Map<String, Object> data, final Collection<ExcelHeader> headers) throws Exception {
		lock_row(() -> {
			for (Iterator<ExcelHeader> it_header = headers.iterator(); it_header.hasNext(); moveRight()) {
				ExcelHeader header = it_header.next();
				if (header.getFieldName() != null || !"".equals(header.getFieldName())) {
					Object value = data.get(header.getFieldName());
					if (value instanceof Collection) {
						fillDatas((Collection<Map<String, Object>>) value, header.getHeaders());
					} else {
						setCell(value);
					}
				}
			}
		});
	}

	private void fillHeaders(final Collection<ExcelHeader> headers) throws Exception {
		lock_col(() -> {
			for (final Iterator<ExcelHeader> it = headers.iterator(); it.hasNext(); moveRight()) {
				ExcelHeader _temp = it.next();
				fillHeader(_temp);
			}
		});
	}

	private void fillHeader(final ExcelHeader header) throws Exception {
		lock_row(new LockAction() {

			@Override
			public void execute() throws Exception {
				if (needsValidate && // e
				getCell().getStringCellValue() != header.getName() && // e
				!getCell().getStringCellValue().equals(header.getName())// e
				) {
					needsValidate = false;
					throw new Error("The format of excel file is not correct.");
				}
				setCell(header.getName());
				if (header.getHeaders() != null) {
					moveDown();
					fillHeaders(header.getHeaders());
				}
			}
		});
	}

	private Object getCellValue() {
		Cell cell = getCell();
		CellType cellType = cell.getCellType() == CellType.FORMULA ? cell.getCachedFormulaResultType() : cell.getCellType();
		if (cellType == CellType._NONE) {
			return null;
		} else if (cellType == CellType.NUMERIC) {
			if (14 <= cell.getCellStyle().getDataFormat() && cell.getCellStyle().getDataFormat() <= 22) {
				return cell.getDateCellValue();
			} else {
				return cell.getNumericCellValue();
			}
		} else if (cellType == CellType.STRING) {
			return cell.getStringCellValue();
		} else if (cellType == CellType.FORMULA) {
			return cell.getCellFormula();
		} else if (cellType == CellType.BLANK) {
			return null;
		} else if (cellType == CellType.BOOLEAN) {
			return cell.getBooleanCellValue();
		} else if (cellType == CellType.ERROR) {
			return null;
		} else {
			return null;
		}
	}

	private Cell getCell(int colNum, int rowNum) {
		Row row = null;
		if ((row = sheet.getRow(rowNum)) == null) {
			row = sheet.createRow(rowNum);
		}
		Cell cell = null;
		if ((cell = row.getCell(colNum)) == null) {
			cell = row.createCell(colNum);
		}
		return cell;
	}

	private Cell getCell() {
		colNumMax = Math.max(colNumMax, colNum);
		rowNumMax = Math.max(rowNumMax, rowNum);
		return getCell(colNum, rowNum);
	}

	private void setCell(Object value) throws Exception {
		Object valueFinal;
		if (value == null) {
			valueFinal = value = "";
		} else {
			valueFinal = value;
		}
		try {
			Cell cell = getCell();
			Method[] methods = Stream.of(cell.getClass().getMethods())// e
					.filter(m -> m.getName().equals("setCellValue"))// e
					.filter(m -> m.getParameterTypes()[0].isAssignableFrom(valueFinal.getClass()))// e
					.collect(Collectors.toSet())// e
					.toArray(new Method[0]);
			if (methods.length > 0) {
				methods[0].invoke(cell, valueFinal);
			} else {
				cell.setCellValue(valueFinal.toString());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private void lock_row(LockAction a) throws Exception {
		int currentRowNum = this.rowNum;// 记录当前行
		a.execute();
		if (currentRowNum != this.rowNum) {
			this.rowNum = currentRowNum;// 回到当前行
		}
	}

	private void lock_col(LockAction a) throws Exception {
		int currentColNum = this.colNum;// 记录当前列
		a.execute();
		if (currentColNum != this.colNum) {
			this.colNum = currentColNum;
		}
	}

	private void merge_row() {
		for (int c = 0; c <= colNumMax; c++) {
			Integer height = null;
			Integer low = null;
			for (int r = 0; r <= rowNumMax; r++) {
				if (sheet.getRow(r) == null || sheet.getRow(r).getCell(c) == null) {
					if (height != null) {
						low = r;
					}
				} else {
					if (height != null && low != null) {
						sheet.addMergedRegion(new CellRangeAddress(height, low, c, c));
						low = null;
					}
					height = r;
				}
			}
			if (height != null && low != null) {
				sheet.addMergedRegion(new CellRangeAddress(height, low, c, c));
				low = null;
			}
		}
	}

	private void merge_col() {
		for (int r = 0; r <= rowNumMax; r++) {
			Integer left = null;
			Integer right = null;
			for (int c = 0; c <= colNumMax; c++) {
				if (sheet.getRow(r) == null || sheet.getRow(r).getCell(c) == null) {
					if (left != null) {
						right = c;
					}
				} else {
					if (left != null && right != null) {
						sheet.addMergedRegion(new CellRangeAddress(r, r, left, right));
					}
					left = c;
				}
			}
			if (left != null && right != null) {
				sheet.addMergedRegion(new CellRangeAddress(r, r, left, right));
			}
		}
	}

	private void moveRight() {
		colNum++;
	}

	private void moveDown() {
		rowNum = rowNumMax + 1;
	}

	private void autoWidth() {
		for (int i = 0; i <= rowNumMax; i++) {
			sheet.autoSizeColumn(i);
		}
	}

	private void removeMerge() {
		for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
			sheet.removeMergedRegion(i);
		}
	}

	public static interface LockAction {

		void execute() throws Exception;
	}

	private void closeCloseable(Closeable c) {
		try {
			c.close();
		} catch (Exception e) {
		}
	}

	/**
	 * -----------TEST---如果看不懂代码请看下面的测试例子---------------
	 * 
	 * @author LIANHANLIN - May 11, 2018 - 11:23:51 PM
	 * @throws Exception
	 */
	public static void main(String[] args) throws Exception {
		testIn();
		testOut();
	}

	public static void testIn() throws Exception {
		Collection<Map<String, Object>> datas = new LinkedList<Map<String, Object>>();
		ExcelPortI excelPrc = new ExcelPort(headers3(0), datas);
		excelPrc.in(new FileInputStream(new File("D:/用户目录/Desktop/test.xls")));
		excelPrc.out(new FileOutputStream("D:/用户目录/Desktop/test_in" + new Date().getTime() + ".xls"));
	}

	public static void testOut() throws Exception {
		ExcelPortI excelPrc = new ExcelPort(headers3(0), itm3lst(data_keys[0], 0));
		excelPrc.out(new FileOutputStream("D:/用户目录/Desktop/test_out" + new Date().getTime() + ".xls"));
	}

	private static String[] data_keys = { "A", "B", "C" };

	private static List<Map<String, Object>> itm3lst(String key, int this_index) {
		List<Map<String, Object>> list = new LinkedList<Map<String, Object>>();
		for (int i = 0; i < 3; i++) {
			Map<String, Object> item = new HashMap<String, Object>();
			list.add(item);
			item.put("id", "item" + key + "[" + i + "].id");
			item.put("name", "item" + key + "[" + i + "].name");
			item.put("code", "item" + key + "[" + i + "].code");
			if (this_index < 2) {
				item.put("Children", itm3lst(data_keys[this_index + 1], this_index + 1));
			}
		}
		return list;
	}

	private static List<ExcelHeader> headers3(int this_index) {
		List<ExcelHeader> headers = new LinkedList<ExcelHeader>();
		String[] keys = { "id", "name", "code" };
		for (int i = 0; i < keys.length; i++) {
			ExcelHeader header = new ExcelHeader(keys[i], keys[i], null);
			headers.add(header);
		}
		if (this_index < 2) {
			headers.add(new ExcelHeader("Children", "Children", headers3(this_index + 1)));
		}
		return headers;
	}
}